﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Ivy.Common.Framework.Base;
using Ivy.Common.Framework.DataAccess;
using Ivy.UserRegistration.Entity;

namespace Ivy.UserRegistration.DataAccess
{
    public class UserProfileDao : IDaoInterface<UserProfile, UserProfileCriteria>
    {
        #region Variables & Constants
        #endregion Variables & Constants

        #region Constructor & Destructor
        public UserProfileDao() : base() { }
        //public UserProfileDao(UserProfile UserProfile)
        //{
        //    this.UserKey = UserProfile.UserKey;
        //    this.UserFullName = UserProfile.UserFullName;
        //    this.UserSex = UserProfile.UserSex;
        //    this.UserDateOfBirth = UserProfile.UserDateOfBirth;
        //    this.UserAlternateEmailID = UserProfile.UserAlternateEmailID;
        //    this.UserContactAddress = UserProfile.UserContactAddress;

        //    this.Sys_DateOfCreation = UserProfile.Sys_DateOfCreation;
        //    this.Sys_DateOfModification = UserProfile.Sys_DateOfModification;
        //    this.Sys_CreatedByID = UserProfile.Sys_CreatedByID;
        //    this.Sys_ModifiedByID = UserProfile.Sys_ModifiedByID;
        //    this.Sys_DeleteType = UserProfile.Sys_DeleteType;
        //    this.Sys_IsDeleted = UserProfile.Sys_IsDeleted;
        //    this.Sys_OrganizationID = UserProfile.Sys_OrganizationID;
        //    this.Sys_WorkstationName = UserProfile.Sys_WorkstationName;
        //    this.EntityState = UserProfile.EntityState;
        //    this.ConcurrencyTimeStamp = UserProfile.ConcurrencyTimeStamp;

        //}

        #endregion

        #region Public Properties
        #endregion Public Properties

        #region Public Methods

        #region IDaoInterface<UserProfile, UserProfileCriteria> Members

        public bool Insert(UserProfile userProfileEntity)
        {
            bool IsOperationSuccessful = false;

            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_UserProfile_Insert";

                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, userProfileEntity.UserKey);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserFullName", ParameterDirection.Input, DbType.String, userProfileEntity.UserFullName);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserSex", ParameterDirection.Input, DbType.String, userProfileEntity.UserSex);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserDateOfBirth", ParameterDirection.Input, DbType.DateTime, userProfileEntity.UserDateOfBirth);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserAlternateEmailID", ParameterDirection.Input, DbType.String, userProfileEntity.UserAlternateEmailID);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserContactNumber", ParameterDirection.Input, DbType.String, userProfileEntity.UserContactNumber);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserContactAddress", ParameterDirection.Input, DbType.String, userProfileEntity.UserContactAddress);

                DatabaseCommon.InsertStoredProcForParameterDatEntityBase(CmdObj, userProfileEntity);
                DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_DateOfCreation", ParameterDirection.Output, DbType.DateTime, DBNull.Value);

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();
                int _NoOfRecords = CmdObj.ExecuteNonQuery();
                if (_NoOfRecords == 1)
                {

                    userProfileEntity.Sys_DateOfCreation = Convert.ToDateTime(CmdObj.Parameters["@Sys_DateOfCreation"].Value);
                    userProfileEntity.Sys_DateOfModification = userProfileEntity.Sys_DateOfCreation;
                    userProfileEntity.EntityState = EntityState.UnChanged;
                    IsOperationSuccessful = true;
                }
                CmdObj.Dispose();
                DbConn.Close();

            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }


            return IsOperationSuccessful;


        }

        public bool Update(UserProfile userProfileEntity)
        {
            bool IsOperationSuccessful = false;
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_UserProfile_Update";
                //CmdObj.CommandText = UserManagementSQLCommandText.InsertUserProfile();
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, userProfileEntity.UserKey);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserFullName", ParameterDirection.Input, DbType.String, userProfileEntity.UserFullName);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserSex", ParameterDirection.Input, DbType.String, userProfileEntity.UserSex);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserDateOfBirth", ParameterDirection.Input, DbType.String, userProfileEntity.UserDateOfBirth);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserAlternateEmailID", ParameterDirection.Input, DbType.String, userProfileEntity.UserAlternateEmailID);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserContactNumber", ParameterDirection.Input, DbType.String, userProfileEntity.UserContactNumber);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserContactAddress", ParameterDirection.Input, DbType.String, userProfileEntity.UserContactNumber);

                DatabaseCommon.UpdateStoredProcForParameterDatEntityBase(CmdObj, userProfileEntity);
                DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_DateOfModification", ParameterDirection.Output, DbType.DateTime, DBNull.Value);

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();
                int _NoOfRecords = CmdObj.ExecuteNonQuery();
                if (_NoOfRecords == 1)
                {


                    userProfileEntity.Sys_DateOfModification = Convert.ToDateTime(CmdObj.Parameters["@Sys_DateOfModification"].Value);
                    userProfileEntity.EntityState = EntityState.UnChanged;
                    IsOperationSuccessful = true;
                }
                CmdObj.Dispose();
                DbConn.Close();


            }
            catch (Exception ex)
            {

                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return IsOperationSuccessful;
        }

        public bool Delete(UserProfile userProfileEntity)
        {
            bool IsOperationSuccessful = false;
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_UserProfile_Delete";
                //CmdObj.CommandText = UserManagementSQLCommandText.InsertUserProfile();
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, userProfileEntity.UserKey);
                DatabaseCommon.DeleteStoredProcdureForParameterDataEntity(CmdObj, userProfileEntity);

                DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_IsDeleted", ParameterDirection.Output, DbType.Boolean, DBNull.Value);
                DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_DateOfModification", ParameterDirection.Output, DbType.DateTime, DBNull.Value);
                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                int _NoOfRecords = CmdObj.ExecuteNonQuery();

                if (_NoOfRecords == 1)
                {
                    userProfileEntity.Sys_DateOfModification = Convert.ToDateTime(CmdObj.Parameters["@Sys_DateOfModification"].Value);
                    userProfileEntity.Sys_IsDeleted = Convert.ToBoolean(CmdObj.Parameters["@Sys_IsDeleted"].Value);
                    userProfileEntity.EntityState = EntityState.UnChanged;
                    IsOperationSuccessful = true;
                }
                CmdObj.Dispose();
                DbConn.Close();
            }
            catch (Exception ex)
            {

                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return IsOperationSuccessful;

        }

        public UserProfile GetByID(string ID)
        {
            UserProfile _UserProfile = null; //IMPORTANT NOTE:- Just declare the variable never create an instance, create instance only when required.
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_UserProfile_GetByID";
                //CmdObj.CommandText = UserManagementSQLCommandText.InsertUserProfile();
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(ID));
                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();
                DbDataReader _DataReader = CmdObj.ExecuteReader();

                if (_DataReader.HasRows)
                {
                    if (_DataReader.Read())
                    {
                        _UserProfile = new UserProfile();
                        TranslateUserProfileFieldsFromReaderToEntity(_UserProfile, _DataReader);
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return _UserProfile;
        }

        public List<UserProfile> GetAll()
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Work in progress - Realest07
        /// </summary>
        /// <param name="userProfileCriteria"></param>
        /// <returns></returns>
        public List<UserProfile> GetByCriteria(UserProfileCriteria userProfileCriteria)
        {
            var _UserProfileList = new List<UserProfile>();

            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_UserProfile_GetByCriteria";
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@UserFullName", ParameterDirection.Input, DbType.String, userProfileCriteria.UserFullName);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserAlternateEmailID", ParameterDirection.Input, DbType.String, userProfileCriteria.UserAlternateEmailID);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserSex", ParameterDirection.Input, DbType.Int32, userProfileCriteria.UserSex);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserDateOfBirth", ParameterDirection.Input, DbType.Int32, userProfileCriteria.UserDateOfBirth);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserContactAddress", ParameterDirection.Input, DbType.Int32, userProfileCriteria.UserContactAddress);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserContactNumber", ParameterDirection.Input, DbType.Int32, userProfileCriteria.UserContactNumber);                

                DatabaseCommon.DefaultStoredProcParameterValuesForGetByCriteria(CmdObj, userProfileCriteria);

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader _DataReader = CmdObj.ExecuteReader();
                if (_DataReader.HasRows)
                {
                    while (_DataReader.Read())
                    {
                        UserProfile _UserProfileTemp = new UserProfile();
                        this.TranslateUserProfileFieldsFromReaderToEntity(_UserProfileTemp, _DataReader);

                        _UserProfileList.Add(_UserProfileTemp);
                    }

                }
                CmdObj.Dispose();
                DbConn.Dispose();
            }
            catch (Exception ex)
            {

                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }

            return _UserProfileList;
        }


        #endregion IDaoInterface<User,UserCriteria> Members

        #endregion Public Methods

        #region Events
        #endregion Events

        #region Private Methods
        private void TranslateUserProfileFieldsFromReaderToEntity(UserProfile pUserProfile, DbDataReader pDbDataReader)
        {
            try
            {
                pUserProfile.UserKey = Convert.ToInt32(pDbDataReader["UserKey"]); ;
                pUserProfile.UserFullName = Convert.ToString(pDbDataReader["UserFullName"]);
                pUserProfile.UserSex = Convert.ToString(pDbDataReader["UserSex"]);
                pUserProfile.UserDateOfBirth = Convert.ToDateTime(pDbDataReader["UserDateOfBirth"]);
                pUserProfile.UserContactNumber = Convert.ToString(pDbDataReader["UserContactNumber"]);
                pUserProfile.UserContactAddress = Convert.ToString(pDbDataReader["UserContactAddress"]);
                pUserProfile.UserAlternateEmailID = Convert.ToString(pDbDataReader["UserAlternateEmailID"]);

                DatabaseCommon.TranslateSystemFieldsFromReaderToEntity(pUserProfile, pDbDataReader);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
        }

        //private void AdapterCommandBuild()
        //{
        //    try
        //    {
        //        DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
        //        DbCommand CmdObj = DbConn.CreateCommand();// DataAccessProviderUtility.CreateCommand;
        //        DbDataAdapter lDataAdapter = DataAccessProviderUtility.CreateDataAdapter();
        //        DbCommandBuilder lCommandBuilder = DataAccessProviderUtility.CreateCommandBuilder();
        //        DataTable lDataTable = new DataTable("UserMaster");
        //        CmdObj.CommandText = "SELECT * FROM UserMaster WHERE UserKey = NULL";
        //        CmdObj.CommandTimeout = 10;
        //        lDataAdapter.SelectCommand = CmdObj;
        //        lDataAdapter.FillSchema(lDataTable, SchemaType.Source);
        //        lCommandBuilder.DataAdapter = lDataAdapter;
        //        lDataAdapter.InsertCommand = lCommandBuilder.GetInsertCommand();
        //        lDataAdapter.UpdateCommand = lCommandBuilder.GetUpdateCommand();
        //        lDataAdapter.DeleteCommand = lCommandBuilder.GetDeleteCommand();
        //        //using (DbCommand cmd = conn.CreateCommand())
        //        //    {
        //        //        cmd.CommandType = CommandType.Text;
        //        //        cmd.CommandText = "select * from " + tableName;
        //        //        cmd.CommandTimeout = 10;
        //        //        using (DbDataAdapter da = factory.CreateDataAdapter())
        //        //        {
        //        //            DbCommandBuilder cb = factory.CreateCommandBuilder();
        //        //            da.SelectCommand = cmd;
        //        //            DataTable dt = new DataTable();
        //        //            da.FillSchema(dt, SchemaType.Source);
        //        //            cb.DataAdapter = da;
        //        //            DbCommand[] cmds = new DbCommand[3]
        //        //            cmds[0] = cb.GetUpdateCommand();
        //        //            cmds[1] = cb.GetDeleteCommand();
        //        //            cmds[2] = cb.GetInsertCommand();
        //        //        }
        //        //    }

        //    }
        //    catch (Exception ex)
        //    {
        //        System.Diagnostics.Debug.Print(ex.Message);
        //        throw ex;
        //    }

        //}

        #endregion Private Methods
    }
}
